package com.shengruan.admin.jdbc;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.shengruan.admin.vo.Product;
import com.shengruan.admin.vo.UserFavorites;
import com.shengruan.framework.jdbc.BaseJdbc;
import com.shengruan.framework.jdbc.BeanListHandler;


public class UserFavoritesJdbc extends BaseJdbc {

    Logger logger = LoggerFactory.getLogger(getClass());

    public static final String USERFAVORITES_INSERT = "insert into user_favorites (userId, productId, creationTime)values(?, ?, ?)";


    public void save(UserFavorites userFavorites) {
        Connection conn = this.getConnection();
        Object[] pram = {userFavorites.getUserId(), userFavorites.getProductId(), userFavorites.getCreationTime()};
        try {
            this.execute(conn, USERFAVORITES_INSERT, pram);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            this.close(conn);
        }
    }


    /**
     *
     * @param userId
     * @return
     */
    public int getRows(int userId) {
        Connection conn = this.getConnection();
        StringBuffer sql = new StringBuffer("select count(p.id) FROM user_favorites uf LEFT JOIN product p on uf.productId = p.id where uf.userId = ?");
        Object[] pram = {userId};
        int count = 0;
        try {
            count = this.getRows(conn, sql.toString(), pram);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            this.close(conn);
        }
        return count;
    }


     /**
     *
     * @param userId
     * @return
     */
    public int getRows(int userId, int productId) {
        Connection conn = this.getConnection();
        StringBuffer sql = new StringBuffer("select count(p.id) FROM user_favorites uf LEFT JOIN product p on uf.productId = p.id where uf.userId = ? and p.id = ?");
        Object[] pram = {userId, productId};
        int count = 0;
        try {
            count = this.getRows(conn, sql.toString(), pram);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            this.close(conn);
        }
        return count;
    }

    /**
     * @param pageSize
     * @param currentPage
     * @param userId
     * @return
     */
    public List<Product> findWithPage(int currentPage, int pageSize, int userId) {
        List<Product> productList = new ArrayList<Product>();
        Connection conn = this.getConnection();
        StringBuffer sql = new StringBuffer("SELECT p.* FROM user_favorites uf LEFT JOIN product p ON uf.productId = p.id WHERE uf.userId = "+userId+" order by uf.creationTime desc limit ?,?");
        try {
            int start = (currentPage - 1) * pageSize;
            productList = this.query(conn, sql.toString(), new BeanListHandler<Product>(Product.class), start, pageSize);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            this.close(conn);
        }
        return productList;
    }


    public List<Product> findAll(int userId) {
        List<Product> productList = new ArrayList<Product>();
        Connection conn = this.getConnection();
		StringBuffer sql = new StringBuffer("SELECT p.* FROM user_favorites uf LEFT JOIN product p ON uf.productId = p.id WHERE uf.userId = ? order by uf.creationTime desc");
        Object[] pram = {userId};
		try {
			productList = this.query(conn, sql.toString(), new BeanListHandler<Product>(Product.class), pram);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			this.close(conn);
		}
		return productList;
    }

    public void deleteById(int userId, int productId) {
        Connection conn = this.getConnection();
        Object[] pram = {userId, productId};
        StringBuffer sql = new StringBuffer("delete from user_favorites where userId = ? and productId = ? ");
        try {
            this.execute(conn, sql.toString(), pram);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            this.close(conn);
        }
    }
    
    /***
     * 根据商品ID删除
     */
    public void deleteByProductId(int productId)
    {
    	 Connection conn = this.getConnection();
         Object[] pram = {productId};
         StringBuffer sql = new StringBuffer("delete from user_favorites where productId = ? ");
         try {
             this.execute(conn, sql.toString(), pram);
         } catch (SQLException e) {
             e.printStackTrace();
         } finally {
             this.close(conn);
         }
    }
}
